The present description relies on technical terms used in connection with operation of a spreadsheet program, including spreadsheet formulas, for an overview see e.g. Microsoft Excel 2002 Bible, John Walkenbach, John Wiley & Sons Inc; ISBN: 0764535838.
Before computers, numerical analyses, particularly financial analyses, were usually prepared on an accountant's columnar pad or spreadsheet, with pencil and calculator in hand. By organizing data into columns and rows, spreadsheets afford the rapid assimilation of information by a reader. The task of preparing a spreadsheet on paper, however, is not quite so fast. Instead, the process tends to be very slow, as each entry must be tediously calculated and entered into the spreadsheet. Since all calculations are the responsibility of the person preparing the spreadsheet, manually prepared spreadsheets are also prone to errors. Hence, preparation of spreadsheets by hand is slow, tedious, and unreliable.
With the advent of microcomputers, a solution was forthcoming in the form of “electronic spreadsheets.” Better known simply as “spreadsheets,” these software programs provide a computerized replacement for the traditional financial modelling tools: The accountant's columnar pad, pencil, and calculator. The improvement represented by a spreadsheet program over these tools may be compared with the improvement provided by a word processor over a typewriter. Spreadsheets offer a dramatic improvement in ease of creating, editing, and using financial models.
A spreadsheet provides millions of cells grouped together in a single sheet or document. A single cell may contain a value, a label or a formula. In a conventional spreadsheet, millions of cells are provided even though only e.g. 25 cells are needed for a specific task. A conventional spreadsheet typically offers 256 columns and 65536 rows amounting to 16,777,216 cells. Most of the cells are not visible. A standard monitor typically displays about 500 cells at the time (33 rows by 15 columns). If a spreadsheet model covers more than 33 rows or 15 columns on a standard monitor, the user will either have to scroll up and down to manage or work with the spreadsheet model or open different windows e.g. showing different parts of the model at the time.
Data is entered into an electronic spreadsheet in much the same way as an accountant enters information on his pad. After the user has positioned a screen cursor at a desired location (a cell), the user can enter alphanumeric information. Besides holding text and numerical information, however, spreadsheet cells can store special instructions or formulas specifying calculations to be performed on the data stored in spreadsheet cells. This is in fact one of the major disadvantages of conventional spreadsheets: Data and the user interface form a complex structure. For example, a single cell may contain a text-based formula hidden underneath the displayed numerical output value of the formula. Hence it is not easy for a spreadsheet user to document a spreadsheet model (i.e. describe to other users how to use a specific model and ensure that the model gives the correct result).
During creation of a particular spreadsheet model, a user enters formulas into cells the same way he or she enters values and labels.
Spreadsheet formulas are fundamental to the creation and operation of a spreadsheet.
A spreadsheet formula has a specific syntax defined by the spreadsheet designer. Typically, a formula begins with a special token (e.g. =, + or @) to distinguish the formula from raw data, followed by tokens as operators (e.g. +−/), values, cell references (e.g. =(B1−B2)/100), and/or built-in functions (e.g. =SUM (A1:A12)). Cell references or addresses can serve as variables in an equation, thereby allowing precise mathematical relationships to be defined between cells.
Before meaningful results of a given spreadsheet can be presented to a user, the formulas of a given spreadsheet model must be evaluated or recalculated. “Recalculation” is the process by which spreadsheet cells, particularly those holding formulas, are updated. In a conventional spreadsheet system, the standard recalculation order is termed “natural”.
Suppose, for example, that it is desired to calculate the price of a good including VAT and shipping costs: The user enters the price of a good in cell A1, the VAT rate in cell B1 and the shipping rate in cell C1. The formula for VAT is entered in cell A4, shipping costs are calculated in cell B4, and total cost is calculated in cell A7, cf. FIG. 1.
By entering different values in cell A1, the user can calculate the total costs. Each time the user enters a new value in cell A1 the spreadsheet system recalculates the model to show the result in cell A7. Interdependencies between the cells dictate how a spreadsheet system actually performs recalculation. Before the spreadsheet system can recalculate the formula in cell A7, the spreadsheet system must recalculate the formulas in the cells, A4 and B4. In the same way, the spreadsheet system must recalculate the formula in cell A4 before it can recalculate the formula in cell B4. This gives the order of recalculation: A4→B4→A7.
Users can use more than one built-in function in a single formula, with one built-in function constituting an argument or parameter to another built-in function. This is termed “nesting” built-in functions. Generally, a user can nest as many built-in functions as desired. For example, the nested function below calculates the average values in cell block C1:C14, and then rounds the average to two decimal places:=ROUND(AVG(C1:C14);2)
Nested built-in functions evaluate from inside out. In the above example, for instance, the AVG is calculated before the ROUND.
When formula syntax and arguments are correct, most built-in functions and formulas recalculate to a numeric value (including dates) or a string value (label or text). If the user does not enter the formulas in a cell correctly, the result of a recalculation is a special value ERR (an error). It is therefore a precondition that the user of a spreadsheet system is familiar with the syntax of a spreadsheet formula. Different spreadsheet systems have different syntaxes to obey.
For example, if a spreadsheet user has to write the formula described in cell B4 in FIG. 1, the user has to know the syntax of an IF built-in function. (Three arguments divided by a special the token ‘;’ or other argument separator (as specified by the implementation) and the meaning of the token ‘<=’ not greater etc.).
With the present trend of employing electronic spreadsheets to model increasingly complex data models, conventional spreadsheets have disadvantages.
One disadvantage is related to the ability of most users to correctly enter and understand complex cell formulas using a conventional electronic spreadsheet system. Some formula-builder systems have been developed to address this problem, e.g. as disclosed in U.S. Pat. No. 5,603,021. Formula-builders constitute a major improvement of spreadsheets and have certainly assisted the user in building a formula with the correct syntax. However, it has not solved the problem that most spreadsheet users find it difficult to manage the syntax of a spreadsheet formula. Consequently the average spreadsheet user has difficulty in understanding the logic of a formula and hence the logic of the spreadsheet.
Another disadvantage is related to the ability to reuse formulas.
For example, the cell formula:=ROUND(AVG(C1:C14);2)is constructed of two built-in functions and operates on the cellblock C1:C14. If it is desired to reuse this formula in a conventional spreadsheet, it must be copied and pasted to a different cell location either in the same spreadsheet or another spreadsheet that are currently residing in the memory of the computer. The copy and paste process will in turn adjust the relative cell references in the spreadsheet formula. If the formula is pasted into a cell located two columns to the right of the cell it was copied from, the cell references is adjusted to E1:E14. It is not possible to save the function as a built-in function under a name, e.g. ROUND_AVG_2 to for reuse on other cell references or as a nested function.
Typically, a financial spreadsheet includes a feature termed Goal Seek.
Consider a sales budget spreadsheet with Sales of 50000 in Cell B2, Costs of 45000 in Cell B3 and the formula=B2−B3 in Cell B4 to calculate gross profit. Further, cell B5 holds the formula=B4/B2 to calculate the gross profit percentage, cf. FIG. 2.
After a recalculation the result, 20% is displayed in cell B5. However, it is desired to reduce the costs to obtain a gross profit percentage of 25%. To obtain the wanted result, the spreadsheet user may enter random values manually into cell B3 until B5 attains the value 25%. Each time the user enters a new value in the cell the spreadsheet model recalculates. Another solution is to use the built-in Goal Seek tool, which is part of most modern spreadsheet systems. A typical Goal Seek tool has a dialog box where the user can enter specifications, cf. FIG. 3.
By entering the specified information in the dialog box and clicking the OK button, the Goal Seek tool starts an iterative process to find the specified value. The process resembles the process of finding the root in an equation. Various root-finding techniques are available, cf. e.g. William Press, Saul A Teukolsky, William T. Vetterling, and Brian P Flannery, Numerical Recipes in C. Cambridge University Press, second edition, 1992.
Regardless of the specific technique used in the spreadsheet system, the goal seek function will insert values in the specified “changing cell” until the desired condition is fulfilled. The condition is fulfilled if the desired value is within a range of ± a delta value from the value specified in the “equal to cell”. If the condition is not fulfilled, the goal seek function will stop after a maximum number of iterations (typical 500 iterations). Each time a new value is inserted in the “changing cell” by the goal seek tool the spreadsheet model is recalculated and the value in the “make cell” is compared with the value in the “equal to cell”. Thus, the goal seek tool is external to the spreadsheet model meaning that the goal seek function is not recalculated each time the sales budget spreadsheet model is recalculated. If the goal seek tool was part of the spreadsheet model, the goal seek process (root finding) would restart each time the spreadsheet model was recalculated after a new value is inserted into the “changing cell”, a process that would lead to an infinite loop. Instead the user must initiate the goal seek loop from a dialog box.
It is a disadvantage of conventional spreadsheets that the goal seek function must be external to the spreadsheet to avoid infinitive recalculation. Further, the inexperienced spreadsheet user finds it difficult to manage a spreadsheet model and specify the demanded input in the goal seek dialog box.